package org.tloss.web.editor.database.editors.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.tloss.web.editor.Utils;
import org.tloss.web.editor.database.editors.DatabaseConnect;
import org.tloss.web.editor.database.editors.jdbc.Column;
import org.tloss.web.editor.database.editors.jdbc.Schema;
import org.tloss.web.editor.database.editors.jdbc.Table;

public class DatabaseService {

	public static String TABLE_CATALOG = "TABLE_CATALOG";
	public static String TABLE_CAT = "TABLE_CAT";
	public static String TABLE_NAME = "TABLE_NAME";
	public static String TABLE_TYPE = "TABLE_TYPE";
	public static String TYPE_CAT = "TYPE_CAT";
	public static String TYPE_SCHEM = "TYPE_SCHEM";
	public static String TYPE_NAME = "TYPE_NAME";
	public static String REMARKS = "REMARKS";
	public static String TABLE_SCHEM = "TABLE_SCHEM";
	public static String SELF_REFERENCING_COL_NAME = "SELF_REFERENCING_COL_NAME";
	public static String REF_GENERATION = "REF_GENERATION";

	public static String COLUMN_NAME = "COLUMN_NAME";
	public static String DATA_TYPE = "DATA_TYPE";
	public static String COLUMN_SIZE = "COLUMN_SIZE";
	public static String DECIMAL_DIGITS = "DECIMAL_DIGITS";
	public static String NUM_PREC_RADIX = "NUM_PREC_RADIX";
	public static String NULLABLE = "NULLABLE";
	public static String COLUMN_DEF = "COLUMN_DEF";
	public static String CHAR_OCTET_LENGTH = "CHAR_OCTET_LENGTH";
	public static String ORDINAL_POSITION = "ORDINAL_POSITION";
	public static String IS_NULLABLE = "IS_NULLABLE";
	public static String SCOPE_CATLOG = "SCOPE_CATLOG";
	public static String SCOPE_SCHEMA = "SCOPE_SCHEMA";
	public static String SCOPE_TABLE = "SCOPE_TABLE";
	public static String SOURCE_DATA_TYPE = "SOURCE_DATA_TYPE";
	public static String IS_AUTOINCREMENT = "IS_AUTOINCREMENT";
	private DatabaseConnect databaseConnect;

	public DatabaseService(DatabaseConnect databaseConnect) {
		this.databaseConnect = databaseConnect;
	}

	/**
	 * Retrieves the schema names available in this database. The results are
	 * ordered by TABLE_CATALOG and TABLE_SCHEM
	 * 
	 * @return <b>null</b> if it's error else list of the schema names available
	 *         in this database
	 */
	public List<Schema> getSchema() {
		Connection connection = null;
		List<Schema> result = new ArrayList<Schema>();
		try {
			connection = databaseConnect.getConnection();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet rs = metaData.getSchemas();
			ResultSetMetaData meta = rs.getMetaData();
			List<String> columns = getAllCollumnName(meta);
			while (rs.next()) {
				Schema schema = new Schema();
				schema.setCatalogName(getSafeString(rs, columns, TABLE_CATALOG));
				schema.setSchemaName(getSafeString(rs, columns, TABLE_SCHEM));
				result.add(schema);
			}
			if (result.size() == 0) {
				rs = metaData.getCatalogs();
				meta = rs.getMetaData();
				columns = getAllCollumnName(meta);
				while (rs.next()) {
					Schema schema = new Schema();
					schema.setCatalogName(getSafeString(rs, columns, TABLE_CAT));
					schema.setSchemaName(getSafeString(rs, columns, TABLE_SCHEM));
					result.add(schema);
				}
			}
		} catch (SQLException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (ClassNotFoundException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (InstantiationException e) {
			Utils.logError("Config database is not correct", e);
		} catch (IllegalAccessException e) {
			Utils.logError("Config database is not correct", e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					Utils.logError("error to close connection", e);
				}
			}
		}
		return result;
	}

	private List<String> getAllCollumnName(ResultSetMetaData metaData)
			throws SQLException {
		List<String> columns = new ArrayList<String>();
		int n = metaData.getColumnCount();
		for (int i = 0; i < n; i++) {
			columns.add(metaData.getColumnName(i + 1));
		}
		return columns;
	}

	private String getSafeString(ResultSet rs, List<String> columns,
			String columnName) throws SQLException {
		if (columns.contains(columnName)) {
			return rs.getString(columnName);
		}
		return null;
	}

	private Integer getSafeInt(ResultSet rs, List<String> columns,
			String columnName) throws SQLException {
		if (columns.contains(columnName)) {
			return rs.getInt(columnName);
		}
		return null;
	}

	public Short getSafeShort(ResultSet rs, List<String> columns,
			String columnName) throws SQLException {
		if (columns.contains(columnName)) {
			return rs.getShort(columnName);
		}
		return null;
	}

	public List<Column> getColumns(String catalog, String schemaPattern,
			String tableNamePattern) {
		Connection connection = null;
		List<Column> result = new ArrayList<Column>();
		try {
			connection = databaseConnect.getConnection();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet rs = metaData.getColumns(catalog, schemaPattern,
					tableNamePattern, null);
			ResultSetMetaData meta = rs.getMetaData();
			List<String> columns = getAllCollumnName(meta);
			while (rs.next()) {
				Column column = new Column();
				column.setCatalogName(getSafeString(rs, columns, TABLE_CAT));
				column.setSchemaName(getSafeString(rs, columns, TABLE_SCHEM));
				column.setTableName(getSafeString(rs, columns, TABLE_NAME));
				column.setColumnName(getSafeString(rs, columns, COLUMN_NAME));
				column.setDataType(getSafeInt(rs, columns, DATA_TYPE));
				column.setTypeName(getSafeString(rs, columns, TYPE_NAME));
				column.setColumnSize(getSafeInt(rs, columns, COLUMN_SIZE));
				column.setDecimalDigits(getSafeInt(rs, columns, DECIMAL_DIGITS));
				column.setNumPrecRadix(getSafeInt(rs, columns, NUM_PREC_RADIX));
				column.setNullable(getSafeInt(rs, columns, NULLABLE));
				column.setRemarks(getSafeString(rs, columns, REMARKS));
				column.setColumnDef(getSafeString(rs, columns, COLUMN_DEF));
				column.setCharOctetLength(getSafeInt(rs, columns,
						CHAR_OCTET_LENGTH));
				column.setOrdinalPosition(getSafeInt(rs, columns,
						ORDINAL_POSITION));
				column.setIsNullable(getSafeString(rs, columns, IS_NULLABLE));
				column.setScopeCatlog(getSafeString(rs, columns, SCOPE_CATLOG));
				column.setScopeSchema(getSafeString(rs, columns, SCOPE_SCHEMA));
				column.setScopeTable(getSafeString(rs, columns, SCOPE_TABLE));
				column.setSourceDataType(getSafeShort(rs, columns,
						SOURCE_DATA_TYPE));
				column.setIsAutoincrement(getSafeString(rs, columns,
						IS_AUTOINCREMENT));
				result.add(column);
			}

		} catch (SQLException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (ClassNotFoundException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (InstantiationException e) {
			Utils.logError("Config database is not correct", e);
		} catch (IllegalAccessException e) {
			Utils.logError("Config database is not correct", e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					Utils.logError("error to close connection", e);
				}
			}
		}
		return result;
	}

	public List<Table> getTables(String catalog, String schemaPattern) {
		Connection connection = null;
		List<Table> result = new ArrayList<Table>();
		try {
			connection = databaseConnect.getConnection();
			DatabaseMetaData metaData = connection.getMetaData();
			ResultSet rs = metaData.getTables(catalog, schemaPattern, null,
					null);
			ResultSetMetaData meta = rs.getMetaData();
			List<String> columns = getAllCollumnName(meta);
			while (rs.next()) {
				Table table = new Table();
				table.setCatalogName(getSafeString(rs, columns, TABLE_CAT));
				table.setSchemaName(getSafeString(rs, columns, TABLE_SCHEM));
				table.setTableName(getSafeString(rs, columns, TABLE_NAME));
				table.setTableType(getSafeString(rs, columns, TABLE_TYPE));
				table.setRemarks(getSafeString(rs, columns, REMARKS));
				table.setTypeCatalog(getSafeString(rs, columns, TYPE_CAT));
				table.setTypeSchema(getSafeString(rs, columns, TYPE_SCHEM));
				table.setTypeName(getSafeString(rs, columns, TYPE_NAME));
				table.setSelfReferencingColName(getSafeString(rs, columns,
						SELF_REFERENCING_COL_NAME));
				table.setRefGeneration(getSafeString(rs, columns,
						REF_GENERATION));
				result.add(table);
			}

		} catch (SQLException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (ClassNotFoundException e) {
			Utils.logError("Config database is not correct", e);
			return null;
		} catch (InstantiationException e) {
			Utils.logError("Config database is not correct", e);
		} catch (IllegalAccessException e) {
			Utils.logError("Config database is not correct", e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					Utils.logError("error to close connection", e);
				}
			}
		}
		return result;
	}
}
